import os
import smtplib
import pandas as pd
import pymysql
from pyhive import hive
from trino import dbapi
from trino.auth import BasicAuthentication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
##定义_bool_()方法


# 设置panda显示参数
pd.set_option("display.max_rows", 10000)
pd.set_option("display.max_columns", 10000)
pd.set_option("display.width", 1000)

# 读取目文件及文件路径
file_name = 'temp.xlsx'
current_dir = os.path.dirname(os.path.abspath(__file__))
file_full_path = os.path.join(current_dir, file_name)

# mysql连接信息
mysql_config = {
    'host': 'rm-wz92r8qt89c31fn40oo.mysql.rds.aliyuncs.com',
    'port': 3306,
    'user': 'bi_rw',
    'password': 'Xjz@&283*-)',
}


class DatabaseConnector:
    def __init__(self, db_type, config):
        self.db_type = db_type
        self.config = config
        if db_type == 'presto':
            self.conn = dbapi.connect(**config)
        elif db_type == 'hive':
            self.conn = hive.connect(**config)
        elif db_type == 'mysql':
            self.conn = pymysql.connect(**config)
        self.cursor = self.conn.cursor()

    def ddl(self, cmd):
        """
        执行ddl语句,
        :param cmd: ddl操作语句
        :return: None
        """
        self.cursor.execute(cmd)
        self.conn.commit()

        # 使用presto的时候, 必须要加上fetchall()之后ddl语句才会执行, 不知道为什么
        if self.db_type == 'presto':
            self.cursor.fetchall()

        print(f"Finish running ddl: {cmd}")

    def dml(self, cmd):
        """
        执行增删改操作
            hive:   操作没有返回集, 使用fetchall会报错, 影响的行数也不可见
            presto: 结果集的类型是rows, insert语句的值是插入的行数, delete操作的值是None
        :param cmd: dml操作语句
        :return: None
        """
        self.cursor.execute(cmd)
        self.conn.commit()
        if self.db_type == 'presto':
            infected_rows = self.cursor.fetchall()[0][0]
            print(f"Finish running dml ({infected_rows} rows infected): {cmd}")
        else:
            print(f"Finish running dml: {cmd}")

    def dql(self, cmd):
        """
        执行查询操作, 需要返回查询结果集的dataframe
        :param cmd: dql操作sql
        :return: dql结果
        """
        self.cursor.execute(cmd)
        dql_desc = self.cursor.description
        dql_rows = self.cursor.fetchall()
        print(f"Finish running dql: {cmd}")
        dql_df = None
        if len(dql_rows) != 0:
            columns = [col[0] for col in dql_desc]
            result = [dict(zip(columns, row)) for row in dql_rows]
            dql_df = pd.DataFrame(result)
            dql_df.columns = columns

        return dql_df


mysql_env = DatabaseConnector('mysql', mysql_config)

#目标数据
query_sql = """ 
select substr(apply_time,1,10) as apply_date
      ,count(distinct order_no) as apply_cnt
      ,sum(amount) as apply_amt
      ,count(distinct user_id) as apply_ppl
      ,count(distinct(if(loan_status  = 'Y' , user_id,null))) as loan_ppl
      ,count(distinct(if(loan_status  = 'Y' , order_no , null))) as loan_cnt
      ,sum(if(loan_status  = 'Y',amount,0)) as loan_amount
from xjz_credit_loan.f_loan fl 
where funding_code in ('HL') group by 1
"""

data_df = mysql_env.dql(query_sql)


if data_df is not None:
    data_df.to_excel(file_name, index=False)
    
    smtp_host = 'smtp.163.com' # 使用网易邮箱的SMTP服务器地址
    sender = '18370569610@163.com' #发件人
    receiver =['3112863970@qq.com'] #收件人
    authorization_code = 'UBemT4MkQePK4T6J' # 发件邮箱对应开通smtp服务的有效授权码
    smtpServer = smtplib.SMTP_SSL(smtp_host, 465) # 使用SSL加密的端口号，如果是TLS则用587
    smtpServer.login(sender,authorization_code) # 使用授权码登录
    
    msg = MIMEMultipart()
    msg['from'] = sender
    msg['to'] = ','.join(receiver) # 收件人邮箱用,拼接;
    msg['subject'] = '测试数据'
    
    content = """
        附件是测试数据，请查收。
    """
    
    msg.attach(MIMEText(content, 'plain', 'utf-8'))
    
    with open(file_full_path, 'rb') as f:
        excel_file = MIMEApplication(f.read())
        excel_file.add_header('Content-Disposition', 'attachment', filename=file_name)
    
    msg.attach(excel_file) # 邮箱添加附件
    
    c = msg.as_string()
    print(c)
    smtpServer.sendmail(sender, receiver, c) # 发送邮件
    smtpServer.quit()
else:
    print('No data generated, Please check')

